import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.billdowney.util.microsoft.office.excel.ExcelReadUtil;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;

public class TaobaoUtil {

	private static String IMG_FORMAT = ".tbi";

	public static void main(String[] args) throws Exception {
		String folderPath = "E:/test/taobao/";
		String fileName = "test.xls";
		readUtil(folderPath, fileName);
	}

	public static JSONArray readUtil(String folderPath, String fileName) {
		String filePath = folderPath + "/" + fileName;
		String[] fileInfo = fileName.split("\\.");
		Map<String, List<List<String>>> excelData = ExcelReadUtil.readExcel(filePath);
		Set<String> keys = excelData.keySet();
		List<String> filds = new ArrayList<>();
		List<String> descs = new ArrayList<>();
		int i = 0, j = 0;
		JSONArray excelArray = new JSONArray();
		for (String key : keys) {
			List<List<String>> sheetData = excelData.get(key);
			i = 0;
			for (List<String> rowData : sheetData) {
				if (i == 0) {
					i++;
					continue;
				}
				j = 0;
				JSONObject data = new JSONObject();
				for (String cellData : rowData) {
					if (i == 1) {
						filds.add(cellData);
					} else if (i == 2) {
						descs.add(cellData);
					} else {
						JSONObject cellJson = new JSONObject();
						cellJson.put("value", cellData);
						cellJson.put("fild", filds.get(j));
						cellJson.put("desc", descs.get(j));
						data.put(filds.get(j), cellJson);
					}
					j++;
				}
				i++;
				if (i > 3) {
					excelArray.add(data);
				}
			}
		}
		for (int k = 0; k < excelArray.size(); k++) {
			JSONObject cellData = excelArray.getJSONObject(k).getJSONObject("picture");
			String[] imgSrcs = cellData.getStr("value").split(";");
			String newImgSrc = "";
			for (int l = 0; l < imgSrcs.length; l++) {
				String imgSrc = imgSrcs[l];
				String img = imgSrc.split(":")[0];
				imgSrcs[l] = imgSrcs[l].replace(img, folderPath + "/" + fileInfo[0] + "/" + img + IMG_FORMAT);
				newImgSrc += imgSrcs[l] + ";";
			}
			cellData.put("picture", newImgSrc);
			excelArray.getJSONObject(k).put("picture", cellData);
		}
		return excelArray;
	}

}
